package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.amado.easyui2.dao.CoachCarDao;
import com.amado.easyui2.dao.impl.StudentDaoImpl.ShopRowMapper;
import com.amado.easyui2.domain.CoachCarEntity;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;
@Repository
public class CoachCarDaoImpl implements CoachCarDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	@Override
	public void add(CoachCarEntity lcn) {

		String sql = "insert into t_coach_car(bh_abbreviate,learn_card_number,old_learn_card_number,car_coloc,carrriage_number,engine_number,training_model,paraphrase_way,index_number_number,body_color_car,automobile_brand,car_type,create_name,update_name)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		getJdbcTemplate().update(sql, lcn.getBhAbbreviate(), lcn.getLearnCardNumber(), lcn.getOldLearnCardNumber(),lcn.getCarColoc(),lcn.getCarrriageNumber(),lcn.getEngineNumber(),lcn.getTrainingModel(),lcn.getParaphraseWay(),lcn.getIndexNumberNumber(),lcn.getBodyColorCar(),lcn.getAutomobileBrand(),lcn.getCarType(),lcn.getCreateName(),lcn.getUpdateName());
	}

	@Override
	public CoachCarEntity getById(Long id) {
		String sql = "select * from t_coach_car where id=?";
		return getJdbcTemplate().queryForObject(sql, new Long[] { id }, new ShopRowMapper());
	}
	class ShopRowMapper implements RowMapper<CoachCarEntity> {
		// rs为返回结果集，以每行为单位封装着
		@Override
		public CoachCarEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
			CoachCarEntity coach = new CoachCarEntity();
			coach.setId(rs.getLong("id"));
			coach.setBhAbbreviate(rs.getString("bh_abbreviate"));
//			bh_abbreviate,learn_card_number,old_learn_card_number,car_coloc,carrriage_number,engine_number,training_model,
//			paraphrase_way,index_number_number,body_color_car,automobile_brand,car_type,create_time,update_time,create_name,update_name
			coach.setLearnCardNumber(rs.getString("learn_card_number"));
			coach.setOldLearnCardNumber(rs.getString("old_learn_card_number"));
			coach.setCarColoc(rs.getString("car_coloc"));
			coach.setCarrriageNumber(rs.getString("carrriage_number"));
			coach.setEngineNumber(rs.getString("engine_number"));
			coach.setTrainingModel(rs.getString("training_model"));
			coach.setParaphraseWay(rs.getString("paraphrase_way"));
			coach.setIndexNumberNumber(rs.getString("index_number_number"));
			coach.setBodyColorCar(rs.getString("body_color_car"));
			coach.setBodyColorCar(rs.getString("automobile_brand"));
			coach.setCarType(rs.getString("car_type"));
			coach.setRegisterDay(rs.getDate("register_day"));
			coach.setComeintoserviceDay(rs.getDate("comeintoservice_day"));
			coach.setYearCarefulTime(rs.getDate("year_careful_time"));
			coach.setYearCarefulState(rs.getString("year_careful_state"));
			coach.setDeleteLogo(rs.getString("delete_logo"));
			coach.setCreateTime(rs.getTimestamp("create_time"));
			coach.setUpdateTime(rs.getTimestamp("update_time"));
			coach.setCreateName(rs.getString("create_name"));
			coach.setUpdateName(rs.getString("update_name"));			
			return coach;
		}
	}

	@Override
	public int delete(List<Long> ids) {
		StringBuilder sql =new StringBuilder("update t_coach_car set delete_logo='Y' where id in (");
		for(int i=0;i<ids.size();i++) {
			if(i==0) {
				sql.append("?");
			}else {
				sql.append(",?");
			}
		}
		sql.append(")");
		return getJdbcTemplate().update(sql.toString(), ids.toArray());
	}

	@Override
	public int update(CoachCarEntity entity) {
		String sql = "update t_coach_car set bh_abbreviate=?,learn_card_number=?,old_learn_card_number=?,car_coloc=?,carrriage_number=?,engine_number=?,training_model=?,paraphrase_way=?,index_number_number=?,body_color_car=?,automobile_brand=?,car_type=?,create_time=?,update_time=?,create_name=?,update_name=? where id=?";
		return getJdbcTemplate().update(sql, entity.getBhAbbreviate(), entity.getLearnCardNumber(),entity.getOldLearnCardNumber(), entity.getCarColoc(), entity.getCarrriageNumber(),entity.getEngineNumber(),entity.getTrainingModel(),entity.getParaphraseWay(),entity.getIndexNumberNumber(),entity.getBodyColorCar(),entity.getAutomobileBrand(),entity.getCarType(),entity.getCreateTime(),entity.getUpdateTime(),entity.getCreateName(),entity.getUpdateName(),entity.getId());
	}

	@Override
	public List<CoachCarEntity> listAll(String tm, String lcn) {
		Connection conn = JDBC.getConn();
		String sql = "select * from t_coach_car where delete_logo='N'";
		PreparedStatement ps = null;
		ResultSet rs = null;
		if (lcn != null && lcn.trim() != "") {
			sql = sql + " and learn_card_number like ?";
		}
		if (tm != null && tm.trim() != "") {
			sql = sql + " and training_model =?";
		}
		System.out.println("sql" + sql);
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (lcn != null && lcn.trim() != "") {
				ps.setString(j++, lcn);
			}
			if (tm != null && tm.trim() != "") {
				ps.setString(j++, "%" + tm + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String bhAbbreviate = rs.getString(i++);
				String deleteLogo = rs.getString(i++);
				String learnCardNumber = rs.getString(i++);
				String oldLearnCardNumber = rs.getString(i++);
				String carColoc = rs.getString(i++);
				String carrriageNumber = rs.getString(i++);
				String engineNumber = rs.getString(i++);
				String trainingModel = rs.getString(i++);
				String paraphraseWay = rs.getString(i++);
				String indexNumberNumber = rs.getString(i++);
				String bodyColorCar = rs.getString(i++);
				String automobileBrand = rs.getString(i++);
				String carType = rs.getString(i++);
				Date registerDay = rs.getTimestamp(i++);
				Date comeintoserviceDay = rs.getTimestamp(i++);
				Date yearCarefulTime = rs.getTimestamp(i++);
				String yearCarefulState = rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				list.add(new CoachCarEntity(id, bhAbbreviate, deleteLogo, learnCardNumber, oldLearnCardNumber, carColoc,
						carrriageNumber, engineNumber, trainingModel, paraphraseWay, indexNumberNumber, bodyColorCar,
						automobileBrand, carType, registerDay, comeintoserviceDay, yearCarefulTime, yearCarefulState,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return list;
	}

	@Override
	public int listAllCount(String tm, String lcn) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(id) FROM t_coach_car where delete_logo='N'";
		if (lcn != null && lcn.trim() != "") {
			sql = sql + " and learn_card_number like ?";
		}
		if (tm != null && tm.trim() != "") {
			sql = sql + " and training_model like ?";
		}
		
		System.out.println("sql" + sql);
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (tm != null && tm.trim() != "") {
				ps.setString(j++, tm);
			}
			if (lcn != null && lcn.trim() != "") {
				ps.setString(j++, "%" + lcn + "%");
			}
			
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public List<CoachCarEntity> listAll(String tm, String lcn, Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_car where delete_logo='N'";
		if (tm != null && tm.trim() != "") {
			sql = sql + " and training_model =?";
		}
		if (lcn != null && lcn.trim() != "") {
			sql = sql + " and learn_card_number like ?";
		}
		sql = sql + "limit ?,?";
		System.out.println("sql" + sql);
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (tm != null && tm.trim() != "") {
				ps.setString(j++, tm);
			}
			if (lcn != null && lcn.trim() != "") {
				ps.setString(j++, "%" + lcn + "%");
			}
			ps.setInt(j++, (page.getCurrentPage() - 1) * page.getRowSize());
			ps.setInt(j++, page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String bhAbbreviate = rs.getString(i++);
				String learnCardNumber = rs.getString(i++);
				String oldLearnCardNumber = rs.getString(i++);
				String carColoc = rs.getString(i++);
				String carrriageNumber = rs.getString(i++);
				String engineNumber = rs.getString(i++);
				String trainingModel = rs.getString(i++);
				String paraphraseWay = rs.getString(i++);
				String indexNumberNumber = rs.getString(i++);
				String bodyColorCar = rs.getString(i++);
				String automobileBrand = rs.getString(i++);
				String carType = rs.getString(i++);
				Date registerDay = rs.getTimestamp(i++);
				Date comeintoserviceDay = rs.getTimestamp(i++);
				Date yearCarefulTime = rs.getTimestamp(i++);
				String yearCarefulState = rs.getString(i++);
				String deleteLogo = rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				list.add(new CoachCarEntity(id, bhAbbreviate, deleteLogo, learnCardNumber, oldLearnCardNumber, carColoc,
						carrriageNumber, engineNumber, trainingModel, paraphraseWay, indexNumberNumber, bodyColorCar,
						automobileBrand, carType, registerDay, comeintoserviceDay, yearCarefulTime, yearCarefulState,createTime,updateTime,createName,updateName));
//				String sql = "SELECT * FROM sys_user where delete_flag='N'";
//				List<Object> listParam = new ArrayList<>();
//				if (departNum != null && departNum.trim() != "") {
//					sql = sql + " and dept_Num =?";
//					listParam.add(departNum);
//				}
//				if (userName != null && userName.trim() != "") {
//					sql = sql + " and user_Num like ?";
//					listParam.add("%"+userName+"%");
//				}
//				sql =sql+"limit ?,?";
//				System.out.println("sql"+sql);		
//				Integer start = (page.getCurrentPage()-1)*page.getRowSize();
//				Integer rowSize = page.getRowSize();
//				listParam.add(start);
//				listParam.add(rowSize);
//				return getJdbcTemplate().query(sql, listParam.toArray(), new ShopRowMapper());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public List<CoachCarEntity> login(CoachCarEntity lcn) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_car where coach_name=? and coach_number=? ";
		List<CoachCarEntity> list = new ArrayList<CoachCarEntity>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				String learnCardNumber = rs.getString(i++);
				String trainingModel = rs.getString(i++);

				list.add(new CoachCarEntity(learnCardNumber, trainingModel));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public CoachCarEntity getByLcn(String lcn) {
		String sql="select * from t_coach_car where learn_card_number=?"; 
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new String[] { lcn }, new ShopRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

//	@Override
//	CoachCarEntity getByLcn(String lcn) {
//		String sql="select * from t_coach_car where learn_card_number=?"; 
//		try {
//			// 如果没有查询到数据会报异常EmptyResultDataAccessException
//			return getJdbcTemplate().queryForObject(sql, new String[] { lcn }, new ShopRowMapper());
//		} catch (EmptyResultDataAccessException e) {
//			
//		}
//	}

}
